--1. Find Id, name and credits of all math majors.

SELECT	lastName, firstName, StuId, credits
FROM	Student
WHERE	major = 'Math';


--2. Find all details of CSC faculty.
SELECT	*
FROM	Faculty
WHERE	department = 'CSC';


--3.a) Find the class numbers of all classes that students are enrolled in.

SELECT	classNumber
FROM		Enroll;


--3.b) Find all the distinct class numbers of classes that students are enrolled in.

SELECT DISTINCT classNumber
FROM	Enroll;


--4. Show all the data in the Student table.

SELECT 	*
FROM	Student;

-5. a) Find faculty names and Ids in order by name.

SELECT	name, facId
FROM	Faculty
ORDER BY  name;


--5.b) add a minor order,by department
SELECT	name, facId
FROM	Faculty
ORDER BY  name, department;


--6. Find names of students who are math majors with more than 30 credits.
SELECT	lastName, firstName
FROM	Student
WHERE	major = 'Math' AND credits > 30;


--7. Find stuid and names of students in class ART103A.
SELECT	Enroll.StuId, lastName, firstName
FROM	Student S, Enroll E
WHERE	classNumber = 'ART103A' and S.stuId = E.stuId;


--8. Find Id and grade of students taught by F110 in order by stuId.
SELECT	stuId, grade
FROM	Class, Enroll
WHERE	facId = 'F110' AND Class.classNumber = Enroll.classNumber 
ORDER BY StuId ASC;


--9. Find course number with names and majors of students enrolled in any course taught by F110.

SELECT	Enroll.classNumber, lastName, firstName, major
FROM	Class, Enroll, Student
WHERE	facId = 'F110'AND Class.classNumber = Enroll.classNumber 
			AND Enroll.StuId = Student.StuId
oRDER BY lastname;


--10. Get a list of courses that meet in the same room.
SELECT 	A.classNumber, A.schedule, A.room, B.classNumber, B.schedule
FROM	Class A, Class B
WHERE	A.room = B.room AND A.classNumber < B.classNumber;


--11. find all combinations of studens and faculty where the student's major is different from the department.
SELECT 	StuId, lastName, firstName, major, facId, name, department
FROM	Student, Faculty
WHERE	Student.major <>  Faculty.department;


--12. a) Find the class number of all clsses taught by Byrne of the math department.
SELECT	classNumber
FROM	Class
WHERE	facId  = (SELECT	facId
		FROM		Faculty
		WHERE	name = 'Byrne'	AND department	= 'Math');


--12.b) same question, as a join
SELECT	classNumber
FROM	Class, Faculty
WHERE	name = 'Byrne' AND department	= 'Math'AND Class.facId = Faculty.facId;


--13. Find names and Ids of all faculty who teach in H221.
SELECT	name, Faculty.facId
FROM	Faculty
WHERE	facId  IN (SELECT	Class.facId
			FROM	Class
			WHERE	room = 'H221');


--14. Get an alphabetical list of names and Ids of all students taught by F110.
SELECT	lastName, firstName, StuId
FROM	Student
WHERE	StuId  IN (SELECT	StuId
			FROM	Enroll
			WHERE	classNumber  IN (SELECT	classNumber 
					FROM	Class
					WHERE	facId = 'F110'))
	ORDER BY lastName, firstName ASC;


--15. Find names of all students enrolled in CS201A (using EXISTS)
SELECT	lastName, firstName
FROM	Student
WHERE	EXISTS	(SELECT	*
		FROM	Enroll
		WHERE	Student.StuId = Enroll.StuId
		AND classNumber = 'CSC201A');


--16. Find names of students not enrolled in CSC201A.
SELECT	lastName, firstName
FROM	Student
WHERE	NOT EXISTS	(SELECT	*
			FROM	Enroll
			WHERE	Student.StuId = Enroll.StuId
				AND classNumber = 'CSC201A');


--17. Find ids of faculty who are in the History dept or who teach in H221.
SELECT	facId
FROM	Faculty
WHERE	department = 'History'
UNION
SELECT	facId
FROM	Class
WHERE	room = 'H221';


--18. Find the number of students in ART103A.

SELECT	COUNT (distinct StuId)
FROM	Enroll
WHERE	classNumber = 'ART103A';


--18(a). Find the number of departments with faculty in them
SELECT	COUNT( distinct department)
FROM	Faculty;


--18(b).find the average number of credits students have.
SELECT	AVG(credits)
FROM	Student;

--18(c).Find the id and name of the student with the largest number of credits.
SELECT	StuId, lastName, firstName
FROM	Student
WHERE	credits =	(SELECT	MAX(credits)
			FROM	Student);

--18(d).Find the Id of the student with the highest grade(using scale A,B,c...)
SELECT	StuId
FROM	Enroll
WHERE	grade =	(SELECT	MIN(grade)
		 FROM 	Enroll);

--18(e).Find names and Ids of students with fewer than average credits.
SELECT	lastName, firstName, StuId
FROM	Student
WHERE	credits < 	(SELECT	 AVG(credits)
			FROM	Student);


--19. For each student, show Id and the number of courses taken (not credits).
SELECT	StuId,'Number of courses =', credits/3
FROM	Student;


--20. For each class, show the number of students enrolled.
SELECT	classNumber, COUNT(*)
FROM	Enroll
GROUP BY  classNumber;


--21. Find all courses with fewer than 3 students enrolled.
SELECT	classNumber, count(*)
FROM	Enroll
GROUP BY  classNumber
HAVING	COUNT(*) < 3 ;


--22.  Find details of all math classes.
SELECT	*
FROM	Class
WHERE	classNumber LIKE 'MTH%' ;


--23. Find stuid and class number for all students who have no grade in that class.
SELECT	classNumber,StuId
FROM	Enroll
WHERE	grade is NULL;

